﻿using DataAchive.Common;
using DataAchive.Model.Excel.Export;
using MySql.Data.MySqlClient;
using NPOI.SS.Formula.Functions;
using SoftCommon;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using static NPOI.HSSF.Util.HSSFColor;

namespace DataAchive.Helper
{
    public class NReportHelper
    {

        public static string GetWhereCause(WhereQ whereCause)
        {
            string whereStr=string.Empty;
            StringBuilder sb = new StringBuilder();
            //if(whereCause.Date ==null||whereCause.Date==DateTime.MinValue)
            //{
            //    whereStr= $" WHERE date >= '{DateTime.Now.ToString("yyyy-MM-dd")}' AND date <= '{DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}'";
            //}
            //else
            //{
            //    whereStr = $" WHERE date >= '{whereCause.Date.ToString("yyyy-MM-dd")}' AND date <= '{DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}'";
            //}

           
            //else
            //{
            //    whereStr = $" WHERE date >= '{whereCause.Date.ToString("yyyy-MM-dd")}' AND date <= '{DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}'";
            //}

            whereStr = $" WHERE CreateTime >= '{whereCause.BeginTime.ToString("yyyy-MM-dd HH:mm:ss")}'";
            whereStr= whereStr+ $" AND CreateTime <= '{whereCause.EndTime.ToString("yyyy-MM-dd HH:mm:ss")}'";

            if (!string.IsNullOrWhiteSpace(whereCause.EquitepmentId))
            {
                whereStr += $" AND equipment_id='{whereCause.EquitepmentId}' ";
            }
            if(whereCause.ManyEquitepmentIds!=null && whereCause.ManyEquitepmentIds.Count > 0)
            {
                List<string>eids = new List<string>();
                whereCause.ManyEquitepmentIds.ForEach(m => eids.Add($"'{m}'") );
                  var equipList = string.Join(",", eids);
                whereStr += $" AND equipment_id in ({equipList}) ";
            }

            SoftCommon.Log.Write("where:"+whereStr);
            return whereStr ;
        }
        public static DataSet GetAllDataReportInfo(WhereQ whereq, string connstr,string exSql=null)
        {           
            string wherestr = GetWhereCause(whereq);
            string cmdText=string.Empty;
            if (string.IsNullOrWhiteSpace(exSql))
            {
                 cmdText = AllData + wherestr;
            }
            else
            {
                 cmdText = exSql + wherestr;
            }
        
            using (SQLiteConnection connection = new SQLiteConnection(connstr))
            {
                try
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    cmd.Connection = connection;
                    cmd.CommandText = cmdText;

                    cmd.Connection = connection;

                    using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            da.Fill(ds, "ds");
                            cmd.Parameters.Clear();
                        }
                        catch (Exception ex)
                        {
                            SoftCommon.Log.Write($"SQL异常：{ex.Message}");
                            throw ex;
                        }
                        return ds;
                    }
                }
                catch (Exception e)
                {
                    SoftCommon.Log.Write("GetAllDataReportInfo 连接异常：" + e.Message);
                    MessageBox.Show("连接异常");
                    throw;
                }
               
            }

        }

        public static DataSet GetLocalRowColReport(WhereQ whereq, string connstr)
        {
            string wherestr = GetWhereCause(whereq);
            string cmdText = SmallReportRowCol1+wherestr+SmallReportRowCol2;
            using (SQLiteConnection connection = new SQLiteConnection(connstr))
            {
                try
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    cmd.Connection = connection;
                    cmd.CommandText = cmdText;

                    cmd.Connection = connection;

                    using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            da.Fill(ds, "ds");
                            cmd.Parameters.Clear();
                        }
                        catch (Exception ex)
                        {
                            SoftCommon.Log.Write($"SQL异常：{ex.Message}");
                            throw ex;
                        }
                        return ds;
                    }
                }
                catch (Exception e)
                {
                    SoftCommon.Log.Write("GetRowColReport 连接异常：" + e.Message);
                    MessageBox.Show("连接异常");
                    throw;
                }

            }

        }

        public const string AllData = " SELECT * FROM RecordInfo";


        public static List<Dictionary<string, object>> DataReportTrans(DataTable dataTable)
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
                return null;
            List<Dictionary<string, object>> listPairs = new List<Dictionary<string, object>>();
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                try
                {
                    // 创建一个字典来存储当前行的键值对
                    Dictionary<string, object> rowDict = new Dictionary<string, object>();

                    // 遍历表头列名
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        string columnName = column.ColumnName;

                        // 判断单元格是否为空
                        if (dataTable.Rows[i][columnName] != DBNull.Value)
                        {
                            rowDict[columnName] = dataTable.Rows[i][columnName];
                        }
                        else
                        {
                            rowDict[columnName] = null; // 或者设置默认值
                        }
                    }


                    listPairs.Add(rowDict);
                }
                catch (Exception e)
                {

                    SoftCommon.Log.Write(e.Message);
                    continue;
                }

            }
            return listPairs;

        }

        public static List<TwoReport> DicToList(List<Dictionary<string, object>> listPairs)
        {
            if (listPairs == null || listPairs.Count == 0)
            {
                return new List<TwoReport>();
            }
            List<TwoReport> reports = new List<TwoReport>();
            foreach (var dics in listPairs)
            {
                TwoReport report1 = new TwoReport();
                foreach (KeyValuePair<string, object> item in dics)
                {
                    try
                    {
                        if (item.Key.Equals("lotid", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.LotId = "";
                            }
                            else
                            {
                                report1.LotId = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("CreateTime", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.CreateTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.CreateTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("Company", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.Company = "";
                            }
                            else
                            {
                                report1.Company = item.Value.ToString();
                            }
                        }
                     
                        else if (item.Key.Equals("MachineId", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.MachineId = "";
                            }
                            else
                            {
                                report1.MachineId = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("Operator", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.Operator = "";
                            }
                            else
                            {
                                report1.Operator = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ElSourcePic", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElSourcePic ="";
                            }
                            else
                            {
                                report1.ElSourcePic = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ElMarkPic", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElMarkPic = "";
                            }
                            else
                            {
                                report1.ElMarkPic = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ViSourcePic", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ViSourcePic = "";
                            }
                            else
                            {
                                report1.ViSourcePic = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ViMarkPic", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ViMarkPic = "";
                            }
                            else
                            {
                                report1.ViMarkPic = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ElAiResult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElAiResult = "";
                            }
                            else
                            {
                                report1.ElAiResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ViAiResult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ViAiResult = "";
                            }
                            else
                            {
                                report1.ViAiResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ElManualResult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElManualResult = "";
                            }
                            else
                            {
                                report1.ElManualResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ViManualResult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ViManualResult = "";
                            }
                            else
                            {
                                report1.ViManualResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("RemoteResTime", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.RemoteResTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.RemoteResTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("RemoteReuslt", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.RemoteReuslt = "";
                            }
                            else
                            {
                                report1.RemoteReuslt = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ElMes", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElMes = "";
                            }
                            else
                            {
                                report1.ElMes = item.Value.ToString();
                            }
                        }              
                        else if (item.Key.Equals("ViMes", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ViMes = "";
                            }
                            else
                            {
                                report1.ViMes = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("IsMes", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.IsMes = "否";
                            }
                            else
                            {
                                report1.IsMes = item.Value.ToString()=="1"?"是":"否";
                            }
                        }
                        else if (item.Key.Equals("PlcWriteTime", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.PlcWriteTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.PlcWriteTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("ElDefectAi", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElDefectAi = "";
                            }
                            else
                            {
                                report1.ElDefectAi = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ViDefectAi", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ViDefectAi = "";
                            }
                            else
                            {
                                report1.ViDefectAi = item.Value.ToString();
                            }
                        }

                    }
                    catch (Exception ee)
                    {

                        SoftCommon.Log.Write("transfer err:" + ee.Message);
                        continue;
                    }


                }

                reports.Add(report1);
            }

            return reports;

        }


        public const string SmallReportAll = "select windowtype, Barcode,Machineid ,CreateTime,saveBarcode,AiDefects from allDefectInfo";
        public const string SmallReportRowCol1= "  SELECT \r\n    max(barcode) as Barcode,\r\n    max(machineid) as Machineid,\r\n    max(createTime) as CreateTime,\r\n    saveBarcode as saveBarcode,\r\n    MAX(CASE WHEN windowtype = 'EL' THEN AiDefects END) AS ElAiDefect,\r\n    MAX(CASE WHEN windowtype = 'ViFront' THEN AiDefects END) AS ViAiDefect,\r\n    MAX(CASE WHEN windowtype = 'ViBack' THEN AiDefects END) AS VbAiDefect,\r\n     MAX(CASE WHEN windowtype = 'EL' THEN AiResult END) AS ElAiResult,\r\n    MAX(CASE WHEN windowtype = 'ViFront' THEN AiResult END) AS ViAiResult,\r\n    MAX(CASE WHEN windowtype = 'ViBack' THEN AiResult END) AS VbAiResult\r\nFROM \r\n    allDefectInfo ";
        public const string SmallReportRowCol2 = "GROUP BY  saveBarcode";
        public static List<LocalSmallReportAll> DicToSmallReportAlls(List<Dictionary<string, object>> listPairs)
        {
            if (listPairs == null || listPairs.Count == 0)
            {
                return new List<LocalSmallReportAll>();
            }
            List<LocalSmallReportAll> reports = new List<LocalSmallReportAll>();
            foreach (var dics in listPairs)
            {
                LocalSmallReportAll report1 = new LocalSmallReportAll();
                foreach (KeyValuePair<string, object> item in dics)
                {
                    try
                    {
                        if (item.Key.Equals("windowtype", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.WindowType = "";
                            }
                            else
                            {
                                report1.WindowType = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("CreateTime", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.CreateTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.CreateTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("Barcode", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.Barcode = "";
                            }
                            else
                            {
                                report1.Barcode = item.Value.ToString();
                            }
                        }

                        else if (item.Key.Equals("MachineId", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.MachineId = "";
                            }
                            else
                            {
                                report1.MachineId = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("saveBarcode", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.SaveBarcode = "";
                            }
                            else
                            {
                                report1.SaveBarcode = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("AiDefects", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.AiDefects = "";
                            }
                            else
                            {
                                report1.AiDefects = item.Value.ToString();
                            }
                        }
                       

                    }
                    catch (Exception ee)
                    {

                        SoftCommon.Log.Write("transfer err:" + ee.Message);
                        continue;
                    }


                }

                reports.Add(report1);
            }

            return reports;
        }

        public static List<SmallReportRowCol> DicToSmallReportColRow(List<Dictionary<string, object>> listPairs)
        {
            if (listPairs == null || listPairs.Count == 0)
            {
                return new List<SmallReportRowCol>();
            }
            List<SmallReportRowCol> reports = new List<SmallReportRowCol>();
            foreach (var dics in listPairs)
            {
                SmallReportRowCol report1 = new SmallReportRowCol();
                foreach (KeyValuePair<string, object> item in dics)
                {
                    try
                    {
                       
                        if (item.Key.Equals("CreateTime", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.CreateTime = DateTime.MinValue;
                            }
                            else
                            {
                                report1.CreateTime = Convert.ToDateTime(item.Value);
                            }
                        }
                        else if (item.Key.Equals("Barcode", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.Barcode = "";
                            }
                            else
                            {
                                report1.Barcode = item.Value.ToString();
                            }
                        }

                        else if (item.Key.Equals("MachineId", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.MachineId = "";
                            }
                            else
                            {
                                report1.MachineId = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("saveBarcode", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.SaveBarcode = "";
                            }
                            else
                            {
                                report1.SaveBarcode = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ElAiDefect", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElAiDefects = "";
                            }
                            else
                            {
                                report1.ElAiDefects = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ViAiDefect", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ViAiDefects = "";
                            }
                            else
                            {
                                report1.ViAiDefects = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("VbAiDefect", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VbAiDefects = "";
                            }
                            else
                            {
                                report1.VbAiDefects = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ElAiResult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ElAiResult = "";
                            }
                            else
                            {
                                report1.ElAiResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("ViAiResult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.ViAiResult = "";
                            }
                            else
                            {
                                report1.ViAiResult = item.Value.ToString();
                            }
                        }
                        else if (item.Key.Equals("VbAiResult", StringComparison.OrdinalIgnoreCase))
                        {
                            if (item.Value is null)
                            {
                                report1.VbAiResult = "";
                            }
                            else
                            {
                                report1.VbAiResult = item.Value.ToString();
                            }
                        }
                    }
                    catch (Exception ee)
                    {

                        SoftCommon.Log.Write("transfer err:" + ee.Message);
                        continue;
                    }


                }

                reports.Add(report1);
            }

            return reports;
        }
    }
}

